-- create the audit trail table

create table enrollaudit(
dateandTimeOfUpdate	date,
userId		varchar2(20),
oldStuid	varchar2(6),
oldClassNumber	varchar2(8),
oldGrade	varchar2(2),
newGrade	varchar2(2),
constraint dateTimeUpdate_userId_pk primary key(dateandTimeOfUpdate,userid) );


--create the trigger

CREATE OR REPLACE TRIGGER EnrollAuditTrail 
	BEFORE UPDATE OF grade ON Enroll
	FOR EACH ROW


	BEGIN
		INSERT INTO EnrollAudit
			VALUES(SYSDATE, USER,:OLD.stuId, :OLD.classNumber, :OLD.grade, :NEW.grade);
	exception
		when dup_val_on_index then 
		DBMS_OUTPUT.PUT_LINE('Duplicate audit record');
		when others then
		DBMS_OUTPUT.PUT_LINE('Error-' || SQLERRM);		

	END;


-- test the trigger

commit;

update enroll set grade ='A';

select * from enrollaudit;

rollback;